package com.xdjmall.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import org.junit.jupiter.api.Test;

import com.xdjmall.dao.OrderDao;
import com.xdjmall.entity.Order;
import com.xdjmall.util.DBUtils;
import com.xdjmall.util.QueryCondition;
import com.xdjmall.util.SQLHandler;

public class OrderDaoImpl implements OrderDao {

	@Override
	public Integer insert(Order t) throws SQLException {
		// 王浩
		String sql = "insert into `order`(order_no,user_id,total_price,pay_status,pay_type) values(?,?,?,?,?)";
		try (Connection c = DBUtils.getConnection(); 
			PreparedStatement ps = c.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);) {
			ps.setString(1, t.getOrderNo());
			ps.setInt(2, t.getUserId());
			ps.setInt(3, t.getTotalPrice());
			ps.setInt(4, t.getPayStatus());
			ps.setInt(5, t.getPayType());
			int row = ps.executeUpdate();
			ResultSet keySet = ps.getGeneratedKeys();
			if (keySet.next()) {
				t.setOrderId(keySet.getInt(1));
			}
			return row;
		}
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Integer update(Order t) throws SQLException {
		// TODO 王浩
		String sql = "update `order` set ";
		if (t.getOrderNo() != null) {
			sql += " order_no = ?,";
		}
		if (t.getUserId() != null) {
			sql += " user_id = ?,";
		}
		if (t.getTotalPrice() != null) {
			sql += " total_price = ?,";
		}
		if (t.getPayStatus() != null) {
			sql += " pay_status = ?,";
		}
		if (t.getPayType() != null) {
			sql += " pay_type = ?,";
		}
		if (t.getOrderStatus() != null) {
			sql += " order_status = ?,";
		}
		if (t.getExtraInfo() != null) {
			sql += " extra_info = ?,";
		}
		if (t.getUserAddress() != null) {
			sql += " user_address = ?,";
		}
		// 截取并删除逗号 substring 包前不包后
		sql += " update_time=now() where order_id=? and is_deleted=0";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (t.getOrderNo() != null) {
				ps.setString(num++, t.getOrderNo());
			}
			if (t.getUserId() != null) {
				ps.setInt(num++, t.getUserId());
			}
			if (t.getTotalPrice() != null) {
				ps.setInt(num++, t.getTotalPrice());
			}
			if (t.getPayStatus() != null) {
				ps.setInt(num++, t.getPayStatus());
			}
			if (t.getPayType() != null) {
				ps.setInt(num++, t.getPayType());
			}
			if (t.getOrderStatus() != null) {
				ps.setInt(num++, t.getOrderStatus());
			}
			if (t.getExtraInfo() != null) {
				ps.setString(num++, t.getExtraInfo());
			}
			if (t.getUserAddress() != null) {
				ps.setString(num++, t.getUserAddress());
			}
			ps.setInt(num, t.getOrderId());
			return ps.executeUpdate();
		}
	}

	@Override
	public Order selectById(Integer id) throws SQLException {
		// TODO 王浩
		String sql = "select order_id,order_no,user_id,total_price,pay_status,pay_type,"
				+ "order_status,extra_info,user_address from `order` where is_deleted=0 and order_id=?";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Order o = new Order();
				o.setOrderId(rs.getInt(1));
				o.setOrderNo(rs.getString(2));
				o.setUserId(rs.getInt(3));
				o.setTotalPrice(rs.getInt(4));
				o.setPayStatus(rs.getInt(5));
				o.setPayType(rs.getInt(6));
				o.setOrderStatus(rs.getInt(7));
				o.setExtraInfo(rs.getString(8));
				o.setUserAddress(rs.getString(9));
				return o;
			}
		}
		return null;
	}

	@Override
	public Integer selectCount() throws SQLException {
		// TODO 王浩
		String sql = "select count(*) from `order` ";
		Connection c = DBUtils.getConnection();
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			int count = rs.getInt(1);
		}
		return rs.getInt(1);
	}

	@Override
	public List<Order> selectByPage(QueryCondition condition) throws SQLException {
		// 不实现
		return null;
	}

	@Override
	public Order selectByOrderNo(String orderNo) throws SQLException {
		// TODO 宣航
		String sql = "select order_id,order_no,user_id,total_price,pay_status,pay_type,"
				+ "pay_time,order_status,extra_info,user_address,is_deleted,create_time,"
				+ "update_time from `order` where is_deleted= 0 and order_no = ? " + " order by create_time desc ";
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setString(1, orderNo);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Order o = new Order();
				o.setOrderId(rs.getInt(1));
				o.setOrderNo(rs.getString(2));
				o.setUserId(rs.getInt(3));
				o.setTotalPrice(rs.getInt(4));
				o.setPayStatus(rs.getInt(5));
				o.setPayType(rs.getInt(6));
				o.setPayTime(rs.getDate(7).toLocalDate());
				o.setOrderStatus(rs.getInt(8));
				o.setExtraInfo(rs.getString(9));
				o.setUserAddress(rs.getString(10));
				o.setIsDeleted(rs.getBoolean(11));
				o.setCreateTime(rs.getDate(12).toLocalDate());
				o.setUpdateTime(rs.getDate(13).toLocalDate());
				return o;
			}
		}
		return null;
	}

	@Test
	public void test() throws SQLException {
		QueryCondition qc = new QueryCondition();
		qc.put("userId", 1);
		qc.setPage(2);
		qc.setPageSize(2);
		int row = this.selectTotalOrders(qc);
		System.out.println(row);
	}

	@Override
	public List<Order> selectOrderList(QueryCondition condition) throws SQLException {
		// TODO 宣航
		List<Order> list = new ArrayList<>();
		String sql = "select order_id,order_no,user_id,total_price,pay_status,pay_type,"
				+ "pay_time,order_status,extra_info,user_address,is_deleted,create_time,"
				+ "update_time from `order` where is_deleted= 0 ";
		String orderNo = null;
		Integer userId = null;
		Integer payType = null;
		Integer orderStatus = null;
		LocalDate startTime = null;
		LocalDate endTime = null;
		Integer page = null;
		Integer pageSize = null;

		if (condition != null) {
			orderNo = (String) condition.get("orderNo");
			userId = (Integer) condition.get("userId");
			payType = (Integer) condition.get("payType");
			orderStatus = (Integer) condition.get("orderStatus");
			startTime = (LocalDate) condition.get("startTime");
			endTime = (LocalDate) condition.get("endTime");
			page = condition.getPage();
			pageSize = condition.getPageSize();

			if (orderNo != null) {
				sql += " and order_no = ?";
			}
			if (userId != null) {
				sql += " and user_id = ?";
			}
			if (payType != null) {
				sql += " and pay_type = ?";
			}
			if (orderStatus != null) {
				sql += " and order_status = ?";
			}
			if (startTime != null) {
				sql += " and create_time >= ?";
			}
			if (endTime != null) {
				sql += " and create_time <= ?";
			}
			sql += " order by create_time desc ";
			if (page != null && pageSize != null) {
				sql += " limit ?,?";
			}
		}

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int num = 1;
			if (orderNo != null) {
				ps.setString(num++, orderNo);
			}
			if (userId != null) {
				ps.setInt(num++, userId);
			}
			if (payType != null) {
				ps.setInt(num++, payType);
			}
			if (orderStatus != null) {
				ps.setInt(num++, orderStatus);
			}
			if (startTime != null) {
				ps.setDate(num++, java.sql.Date.valueOf(startTime));
			}
			if (endTime != null) {
				ps.setDate(num++, java.sql.Date.valueOf(endTime));
			}
			if (page != null && pageSize != null) {
				ps.setInt(num++, (page - 1) * pageSize);
				ps.setInt(num, pageSize);
			}
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Order o = new Order();
				o.setOrderId(rs.getInt(1));
				o.setOrderNo(rs.getString(2));
				o.setUserId(rs.getInt(3));
				o.setTotalPrice(rs.getInt(4));
				o.setPayStatus(rs.getInt(5));
				o.setPayType(rs.getInt(6));
				Date date = rs.getDate(7);
				o.setPayTime(date != null ? date.toLocalDate() : null);
				o.setOrderStatus(rs.getInt(8));
				o.setExtraInfo(rs.getString(9));
				o.setUserAddress(rs.getString(10));
				o.setIsDeleted(rs.getBoolean(11));
				o.setCreateTime(rs.getDate(12).toLocalDate());
				o.setUpdateTime(rs.getDate(13).toLocalDate());
				list.add(o);
			}
		}
		return list.isEmpty() ? Collections.emptyList() : list;
	}

	@Override
	public int selectTotalOrders(QueryCondition condition) throws SQLException {
		// TODO 宣航
		return selectOrderList(condition).size();
	}

	@Override
	public List<Order> selectByIds(List<Integer> orderIds) throws SQLException {
		// TODO 孙琦
		List<Order> orders = new ArrayList<>();
		String sql ="select order_id,order_no,user_id,total_price,pay_status,pay_type,"
				+ "pay_time,order_status,extra_info,user_address,is_deleted,create_time,"
				+ "update_time from `order` where order_id " + SQLHandler.joinIn(orderIds);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Order o = new Order();
				o.setOrderId(rs.getInt(1));
				o.setOrderNo(rs.getString(2));
				o.setUserId(rs.getInt(3));
				o.setTotalPrice(rs.getInt(4));
				o.setPayStatus(rs.getInt(5));
				o.setPayType(rs.getInt(6));
				Date date = rs.getDate(7);
				o.setPayTime(date != null ? date.toLocalDate() : null);
				o.setOrderStatus(rs.getInt(8));
				o.setExtraInfo(rs.getString(9));
				o.setUserAddress(rs.getString(10));
				o.setIsDeleted(rs.getBoolean(11));
				o.setCreateTime(rs.getDate(12).toLocalDate());
				o.setUpdateTime(rs.getDate(13).toLocalDate());
				orders.add(o);
			}
			// 如果查询没有结果，goods是空的，返回一个空集合，否则返回goods
			return orders.isEmpty() ? Collections.emptyList() : orders;
		}
	}

	@Override
	public int checkOut(List<Integer> orderIds) throws SQLException {
		// TODO 孙琦
		String sql = "update `Order` set order_status=3 where order_id " + SQLHandler.joinIn(orderIds);

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int row = ps.executeUpdate();
			return row;
		}
	}

	@Override
	public int closeOrder(List<Integer> orderIds, Integer orderStatus) throws SQLException {
		// TODO 孙琦
		String sql = "update `Order` set order_status=? where order_id " + SQLHandler.joinIn(orderIds);

		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			ps.setInt(1, orderStatus);
			int row = ps.executeUpdate();
			return row;
		}
	}

	@Override
	public int checkDone(List<Integer> orderIds) throws SQLException {
		// TODO 孙琦
		String sql = "update `Order` set order_status=2 where order_id " + SQLHandler.joinIn(orderIds);
		try (Connection c = DBUtils.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
			int row = ps.executeUpdate();
			return row;
		}
	}
}
